import com.alibaba.druid.pool.DruidDataSource;
import org.jetbrains.annotations.NotNull;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

public class MysqlControl {
    public static void main(String[] args) {
        link();
    }

    private static void link() {
        DruidDataSource ds = new DruidDataSource();
        ds.setDriverClassName("com.mysql.cj.jdbc.Driver");
        ds.setUrl("jdbc:mysql://localhost:3306/dataprinciple?useUnicode=true&characterEncoding=utf-8&useSSL=false");
        ds.setUsername("root");
        ds.setPassword("lxm12345");
        try {
            Connection con = ds.getConnection();
            getProjectByCity(con, "北京");
            getProjectByCityAndColor(con, "北京", "红色");
        } catch (SQLException e) {
            System.out.println(e);
        } finally {
            ds.close();
        }
    }

    private static void getProjectByCity(@NotNull Connection con, String city) throws SQLException {
        String sql = "SELECT jno,jname,city FROM project WHERE city=?";

        PreparedStatement pstmt = con.prepareStatement(sql);
        pstmt.setString(1, city);
        // 执行查询并获取 ResultSet
        ResultSet rs = pstmt.executeQuery();
        Map<String, Map<String, String>> resultMap = new HashMap<>();
        int num = 1;
        // 处理查询结果
        while (rs.next()) {
            Map<String, String> map = new HashMap<>();
            String jno = rs.getString("jno");
            map.put("项目编号", jno);
            String jname = rs.getString("jname");
            map.put("项目名称", jname);
            String projectCity = rs.getString("city");
            map.put("项目城市", projectCity);
            resultMap.put("查项目所在城市结果" + num++ + ":", map);
        }
        resultMap.forEach((key, value) -> System.out.println(key + ":\t" + value));

    }


    private static void getProjectByCityAndColor(@NotNull Connection con, String city, String Color) throws SQLException {
        String sql = "SELECT s.sno,s.sname,s.city  AS supplierCity," +
                "p.pno,p.pname,p.color," +
                "pr.jno,pr.jname,pr.city AS projectCity " +
                "FROM part p," +
                "supplier s," +
                "project pr " +
                "WHERE p.color = ? AND pr.city = ?;";
        PreparedStatement pstmt = con.prepareStatement(sql);
        pstmt.setString(1, Color);
        pstmt.setString(2, city);
        // 执行查询并获取 ResultSet
        ResultSet rs = pstmt.executeQuery();
        Map<String, Map<String, String>> resultMap = new HashMap<>();
        int num = 1;
        // 处理查询结果
        while (rs.next()) {
            Map<String, String> map = new HashMap<>();
            String sno = rs.getString("sno");
            map.put("供应商编号", sno);
            String sname = rs.getString("sname");
            map.put("供应商名称", sname);
            String supplierCity = rs.getString("supplierCity");
            map.put("供应商城市", supplierCity);
            String pno = rs.getString("pno");
            map.put("零件编号", pno);
            String pname = rs.getString("pname");
            map.put("零件名称", pname);
            String color = rs.getString("color");
            map.put("颜色", color);
            String jno = rs.getString("jno");
            map.put("项目编号", jno);
            String jname = rs.getString("jname");
            map.put("项目名称", jname);
            String projectCity = rs.getString("projectCity");
            map.put("项目城市", projectCity);
            resultMap.put("联合查询结果" + num++, map);
        }
        resultMap.forEach((key, value) -> System.out.println(key + ":\t" + value));
    }
}
